package dao.employee;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.UUID;

import Test.StringUtil;
import Test.TestDB;

public class Dao extends TestDB{
//  ssss
/* ref-keys: modules.crud1.search:Dao */
    public HashMap SearchEmployees(String key, int index, int size, String sortField, String sortOrder) throws Exception
    {
        //System.Threading.Thread.Sleep(300);
    	if(key == null) key = "";

    	String sql =
			 "select a.*, b.name dept_name, c.name position_name, d.name educational_name \n"
			+"from t_employee a \n"
			+"left join t_department b \n"
			+"on a.dept_id = b.id \n"
			+"left join t_position c \n"
			+"on a.position = c.id \n"
			+"left join t_educational d \n"
			+"on a.educational = d.id \n"
			+"where a.name like '%" + key + "%' \n";

        if (StringUtil.isNullOrEmpty(sortField) == false)
        {
            if ("desc".equals(sortOrder) == false) sortOrder = "asc";
            sql += " order by " + sortField + " " + sortOrder;
        }
        else
        {
            sql += " order by createtime desc";
        }

        ArrayList dataAll = DBSelect(sql);

        ArrayList data = new ArrayList();
        int start = index * size, end = start + size;

        for (int i = 0, l = dataAll.size(); i < l; i++)
        {
            HashMap record = (HashMap)dataAll.get(i);
            if (record == null) continue;
            if (start <= i && i < end)
            {
                data.add(record);
            }
            record.put("createtime", new Timestamp(100,10,10,1,1,1,1));
        }

        HashMap result = new HashMap();
        result.put("data", data);
        result.put("total", dataAll.size());

        //minAge, maxAge, avgAge
        ArrayList ages = DBSelect("select min(age) as minAge, max(age) as maxAge, avg(age) as avgAge from t_employee");
        HashMap ageInfo = (HashMap)ages.get(0);
        result.put("minAge", ageInfo.get("minAge"));
        result.put("maxAge", ageInfo.get("maxAge"));
        result.put("avgAge", ageInfo.get("avgAge"));

        return result;
    }
    public HashMap GetEmployee(String id) throws Exception
    {
    	String sql = "select * from t_employee where id = '"+id+"'";
        ArrayList data = DBSelect(sql);
        return data.size() > 0 ? (HashMap)data.get(0) : null;
    }
    public String InsertEmployee(HashMap user) throws Exception
    {
    	String id = (user.get("id") == null || user.get("id").toString().equals(""))? UUID.randomUUID().toString() : user.get("id").toString();
        user.put("id", id);

        if (user.get("name") == null) user.put("name", "");
        if (StringUtil.isNullOrEmpty(user.get("gender"))) user.put("gender", 0);

    	Connection conn = TestDB.getConn();

    	String sql = "insert into t_employee (id, loginname, name, age, married, gender, birthday, country, city, dept_id, position, createtime, salary, educational, school, email, remarks)"
            + " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		PreparedStatement stmt = conn.prepareStatement(sql);

		stmt.setString(1, ToString(user.get("id")));
		stmt.setString(2, ToString(user.get("loginname")));
		stmt.setString(3, ToString(user.get("name")));
		stmt.setInt(4, ToInt(user.get("age")));
		stmt.setInt(5, ToInt(user.get("married")));
		stmt.setInt(6, ToInt(user.get("gender")));
		System.out.println("xxxx:"+user.get("birthday"));
		stmt.setDate(7, ToDate(user.get("birthday")));
		stmt.setString(8, ToString(user.get("country")));
		stmt.setString(9, ToString(user.get("city")));
		stmt.setString(10, ToString(user.get("dept_id")));
		stmt.setString(11, ToString(user.get("position")));
		stmt.setDate(12, ToDate(user.get("createtime")));
		System.out.println("xxxx222:"+user.get("createtime"));
		System.out.println("xxxx222oooo:"+ToDate(user.get("createtime")));
		stmt.setString(13, ToString(user.get("salary")));
		stmt.setString(14, ToString(user.get("educational")));
		stmt.setString(15, ToString(user.get("school")));
		stmt.setString(16, ToString(user.get("email")));
		stmt.setString(17, ToString(user.get("remarks")));

		stmt.executeUpdate();

        stmt.close();
		conn.close();

        return id;
    }
    public void DeleteEmployee(String userId) throws Exception
    {

		Connection conn = getConn();
		Statement stmt = conn.createStatement();

        String sql = "delete from t_employee where id = \""+userId+"\"";
        stmt.executeUpdate(sql);

		stmt.close();
		conn.close();
    }
    public void UpdateEmployee(HashMap user) throws Exception
    {
        HashMap db_user = GetEmployee(user.get("id").toString());

        Iterator iter = user.entrySet().iterator();
        while (iter.hasNext()) {
            Map.Entry entry = (Map.Entry) iter.next();
            Object key = entry.getKey();
            Object val = entry.getValue();

            db_user.put(key, val);
        }

        DeleteEmployee(user.get("id").toString());
        InsertEmployee(db_user);
    }

}
